
--EXEC SP_LOAD_ALL_DATAENTRY_LOTS '0', '0'
CREATE PROCEDURE SP_LOAD_ALL_DATAENTRY_LOTS
(
	@PROCESS_TYPE VARCHAR(50),
	@VENDOR_NAME  VARCHAR(50)

)
AS
BEGIN
		CREATE TABLE #RESULTS ( LOT_ID INT,PART_NUMBER VARCHAR(50), Lot_Number VARCHAR(50), Fab_Lot_Number VARCHAR(50), PS_TYPE VARCHAR(50), QTY_IN INT, VENDOR_NAME VARCHAR(50), DATE_IN DATETIME ) 
		
		DECLARE @QUERYSTRING	VARCHAR(4000)

		SET @QUERYSTRING = N'
 		INSERT INTO #RESULTS 
 		SELECT	LT.LOT_ID, PART_NUMBER, LOT_NUMBER, FAB_LOT_NUMBER, PS_TYPE,
		(CASE WHEN (PS_TYPE_NAME = ''FAB'' AND PS_TYPE_NAME = ''WAFER STORE'' AND PS_TYPE_NAME = ''SORT'' ) THEN WAFER_QTY_IN
			  ELSE QTY_IN
		 END ), L_VENDOR_NAME, DATE_IN
		FROM LOT_TABLE LT, PROCESS_STEP PS, LOCATION L, PRODUCT P 
		WHERE 
		LT.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND P.PROCESSSTEP_ID = PS.PROCESSSTEP_ID AND
		LT.LOCATION_ID = L.LOCATION_ID AND ( WAFER_QTY_IN > 0 OR QTY_IN > 0 ) 
		AND LT.LOT_CURRENT_STATUS <> ''Close'' AND Lot_Entered_By = 0 '

		IF @PROCESS_TYPE IS NOT NULL AND @PROCESS_TYPE <> '0'
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING + ' AND PS.PS_TYPE = '''+ @PROCESS_TYPE+''''
	 	END
	 	/*IF ( @PROCESSSTEP_ID IS NOT NULL AND @PROCESSSTEP_ID > 0  )
	 	BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND PS.PROCESSSTEP_ID = ' + CONVERT(VARCHAR,@PROCESSSTEP_ID)
	 	END*/
		IF ( @VENDOR_NAME IS NOT NULL AND @VENDOR_NAME <> 'All Vendors' AND @VENDOR_NAME <> '0'  )
		BEGIN
	 		SET @QUERYSTRING = @QUERYSTRING +' AND L.L_VENDOR_NAME = '''+@VENDOR_NAME+''''
	 	END
	
		SET @QUERYSTRING = @QUERYSTRING + ' ORDER BY P.PART_NUMBER, L_VENDOR_NAME, DATE_IN '
		
		EXEC (@QUERYSTRING)

		SELECT LOT_ID,PART_NUMBER, Lot_Number, Fab_Lot_Number, PS_TYPE, QTY_IN, 
			  VENDOR_NAME, CONVERT(VARCHAR,DATE_IN, 101) As DATE_IN FROM #RESULTS
END